package henu.dao.impl;

import henu.bean.Company;
import henu.bean.PInformation;
import henu.dao.PInformationDao;
import henu.util.Dbcp;

import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

public class PInformationDaoImpl implements PInformationDao {

	@Override
	public int add(PInformation p) {
		// TODO 自动生成的方法存根
		int result=0;
		try {
			String sql="insert into pinformation (idCard,comid,phone,regType,realName,paudit,photoPath,address,sex,email,password,question,answer,regTime) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
			QueryRunner qr =new QueryRunner(Dbcp.getDataSource());
			Object[] params ={p.getIdCard(),p.getComid(),p.getPhone(),p.getRegType(),p.getRealName(),p.getPaudit(),p.getPhotoPath(),p.getAddress(),p.getSex(),p.getEmail(),p.getPassword(),p.getQuestion(),p.getAnswer(),p.getRegTime()};
			result =qr.update(sql, params);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}

	@Override
	public int deleteByIdCard(String idcard) {
		int result = 0;
		String sql ="delete from PINFORMATION where idCard=?";
		QueryRunner qr = new QueryRunner(Dbcp.getDataSource());
		try {
			result =qr .update(sql, idcard);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}
	/*根据身份证号或者密码看能否发现一个用户*/
	@Override
	public PInformation findByIdCardOrPhone(String idCard,String phone){
		PInformation p=null;
		try {
			QueryRunner qr=new QueryRunner(Dbcp.getDataSource());
			String sql="select * from pinformation where idCard=? or phone=?";
			Object[] params={idCard,phone};
			p=qr.query(sql, new BeanHandler<PInformation>(PInformation.class), params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return p;
	}
	/*根据身份证号，设置问题和问题答案查找用户*/
	@Override
	public PInformation findByIdCardAndQuestionAndAnswer(String idCard,String question,String answer){
		PInformation p=null;
		try {
			QueryRunner qr=new QueryRunner(Dbcp.getDataSource());
			String sql="select * from pinformation where idCard=? and question=? and answer=?";
			Object[] params={idCard,question,answer};
			p=qr.query(sql, new BeanHandler<PInformation>(PInformation.class), params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return p;
	}
	/*根据身份证号，设置问题和问题答案查找用户*/
	@Override
	public int resetPassword(String idCard,String newPassword){
		int result=0;
		try {
			QueryRunner qr=new QueryRunner(Dbcp.getDataSource());
			String sql="update pinformation set password=? where idCard=?";
			Object[] params={newPassword,idCard};
			result=qr.update(sql, params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}
	/*
	 *登录
	 */
	@Override
	public PInformation login(String phone,String password){
		PInformation p=null;
		try {
			QueryRunner qr=new QueryRunner(Dbcp.getDataSource());
			String sql="select * from pinformation where phone=? and password=?";
			Object[] params={phone,password};
			p=qr.query(sql, new BeanHandler<PInformation>(PInformation.class), params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return p;
	}
	//每次登陆成功更新上一次访问时间
	@Override
	public void updateLastTime(String idCard){
		
		try {
			QueryRunner qr=new QueryRunner(Dbcp.getDataSource());
			String sql="update pinformation set lastTime=? where idCard=?";
			Object[] params={new Date().toLocaleString(),idCard};
			qr.update(sql,params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	//============后台调用==========//
	//查看已审核用户
	@Override
	public List<PInformation> findByProperty(String property, String key,String comid, String sort, int start, int end) {
		
		List<PInformation> list =null;
		String sql = "SELECT * FROM (SELECT tt.*, ROWNUM ro FROM (SELECT * FROM PINFORMATION WHERE "+property +" ='"+key+"' and comid=? AND PAUDIT = '是'  order by regTime asc) tt WHERE ROWNUM <=" + end + ") WHERE ro > " + start + "";
		//System.out.println("sql:"+sql);
		QueryRunner runner = new QueryRunner(Dbcp.getDataSource());
		try {
			list=runner.query(sql, new BeanListHandler<PInformation>(PInformation.class),comid);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	
		return list;
	}
	//查看待审核用户
	@Override
	public List<PInformation> findWaitChkeck(String property, String key,String comid, String sort, int start, int end){
		List<PInformation> list=null;
		String sql="SELECT * FROM (SELECT tt.*, ROWNUM ro FROM (select * from PINFORMATION where "+property+"='"+key+"' and comid=? AND PAUDIT = '否'  order by regTime asc) tt WHERE ROWNUM <="+end+") WHERE ro > "+start+"";
		QueryRunner runner = new QueryRunner(Dbcp.getDataSource());
		try {
			list=runner.query(sql, new BeanListHandler<PInformation>(PInformation.class),comid);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	//审核通过、不予通过、禁用，启用，单个用户
	public int pass(String idCard,String memo1,String paudit) {
		
		int result=0;
		String sql="UPDATE PINFORMATION SET paudit=?,memo1=? WHERE idCard =?";
		QueryRunner qr =new QueryRunner(Dbcp.getDataSource());
		Object[] params={paudit,memo1,idCard};
		try {
			result =qr.update(sql,params);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	
		return result;
	}
	//前台用户查看自己的信息
	public PInformation findByidCard(String idCard){
		PInformation p =null;
		String sql="SELECT * FROM PINFORMATION WHERE idCard =?";
		QueryRunner qr =new QueryRunner(Dbcp.getDataSource());
		Object[] params={idCard};
			try {
				p=qr.query(sql, new BeanHandler<PInformation>(PInformation.class),params);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		return p;
	}
	//前台用户修改自己的信息
	public int update(PInformation p,String idcard){
		//更新方法中暂时去掉了公司名称的更新
		String sql="update pinformation set realName=?,phone=?,regType=?,address=?,regTime=?,email=?,isMaster=?,photoPath=?,paudit=? where idCard=?";
		QueryRunner qr =new QueryRunner(Dbcp.getDataSource());
		Object[] params ={p.getRealName(),p.getPhone(),p.getRegType(),p.getAddress(),p.getRegTime(),p.getEmail(),p.getIsMaster(),p.getPhotoPath(),p.getPaudit(),idcard};
		int result=0;
		try {
			result =qr.update(sql, params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}
	public String findByComid(int comid) {
		Company company=null;
		String sql="SELECT * FROM COMPANY WHERE COMID=?";
		QueryRunner qr =new QueryRunner(Dbcp.getDataSource());
		try {
			company = qr.query(sql, new BeanHandler<Company>(Company.class), comid);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		String companyname=company.getCompanyname();
		return companyname;
	}

	//通过公司名 获取到公司的comid
	@Override
	public int findByCompanyName(String companyname) {
		int comid=0;
		Company company=null;
		String sql="SELECT * FROM COMPANY WHERE COMID=?";
		QueryRunner qr =new QueryRunner(Dbcp.getDataSource());
		try {
			company = qr.query(sql, new BeanHandler<Company>(Company.class), comid);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		comid=company.getComid();
		return comid;
	}

	@Override
	public String findbyid(String idcard) {
		String ismaster=null;
		
		String sql="SELECT ISMASTER FROM PINFORMATION WHERE idCard =?";
		QueryRunner qr =new QueryRunner(Dbcp.getDataSource());
		Object[] params={idcard};
			try {
				ismaster=qr.query(sql, new BeanHandler<PInformation>(PInformation.class),params).getIsMaster();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		return ismaster;
	}

	@Override
	public List<PInformation> ban(String property, String key, String comid,
			String sort, int start, int end) {
		List<PInformation> list=null;
		String sql="SELECT * FROM (SELECT tt.*, ROWNUM ro FROM (select * from PINFORMATION where "+property+"='"+key+"' and comid=? AND PAUDIT = '禁用'  order by regTime asc) tt WHERE ROWNUM <="+end+") WHERE ro > "+start+"";
		QueryRunner runner = new QueryRunner(Dbcp.getDataSource());
		try {
			list=runner.query(sql, new BeanListHandler<PInformation>(PInformation.class),comid);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
}
